﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Text;
using System.Xml;
using System.Xml.XPath;

namespace Songhay.Data.Common
{
    /// <summary>
    /// A few static helper members
    /// for <see cref="System.Data.Common.DbDataReader"/>
    /// and selected <c>System.Data</c> interfaces.
    /// </summary>
    static public class CommonReader
    {
        /// <summary>
        /// Returns a <see cref="System.Data.Common.DbDataReader"/>
        /// based on the <see cref="System.Data.Common.DbConnection"/>.
        /// </summary>
        /// <param name="cnn">The <see cref="System.Data.Common.DbConnection"/>.</param>
        /// <param name="query">The SELECT SQL statement.</param>
        public static DbDataReader GetReader(DbConnection cnn, String query)
        {
            return CommonReader.GetReader(cnn as IDbConnection, query, null) as DbDataReader;
        }

        /// <summary>
        /// Returns an instance of <see cref="System.Data.IDataReader"/>
        /// based on the instance of <see cref="System.Data.Common.DbConnection"/>.
        /// </summary>
        /// <param name="cnn">The <see cref="System.Data.Common.DbConnection"/>.</param>
        /// <param name="query">The SELECT SQL statement.</param>
        public static IDataReader GetReader(IDbConnection cnn, String query)
        {
            return CommonReader.GetReader(cnn, query, null);
        }

        /// <summary>
        /// Returns a <see cref="System.Data.Common.DbDataReader"/>
        /// based on the <see cref="System.Data.Common.DbConnection"/>.
        /// </summary>
        /// <param name="cnn">The <see cref="System.Data.Common.DbConnection"/>.</param>
        /// <param name="query">The SELECT SQL statement.</param>
        /// <param name="parameterCollection">A list of parameters.</param>
        public static DbDataReader GetReader(DbConnection cnn, String query, Dictionary<String, Object> parameterCollection)
        {
            return CommonReader.GetReader(cnn as IDbConnection, query, parameterCollection, 30) as DbDataReader;
        }

        /// <summary>
        /// Returns an instance of <see cref="System.Data.IDataReader"/>
        /// based on the object implementing <see cref="System.Data.IDbConnection"/>.
        /// </summary>
        /// <param name="cnn">The object implementing <see cref="System.Data.IDbConnection"/>.</param>
        /// <param name="query">The SELECT SQL statement.</param>
        /// <param name="parameterCollection">A list of parameters.</param>
        public static IDataReader GetReader(IDbConnection cnn, String query, Dictionary<String, Object> parameterCollection)
        {
            return CommonReader.GetReader(cnn, query, parameterCollection, 30);
        }

        /// <summary>
        /// Returns a <see cref="System.Data.Common.DbDataReader"/>
        /// based on the <see cref="System.Data.Common.DbConnection"/>.
        /// </summary>
        /// <param name="cnn">The <see cref="System.Data.Common.DbConnection"/>.</param>
        /// <param name="query">The SELECT SQL statement.</param>
        /// <param name="parameterCollection">A list of parameters.</param>
        /// <param name="timeout">Command timeout in seconds.</param>
        public static DbDataReader GetReader(DbConnection cnn, String query, Dictionary<String, Object> parameterCollection, int timeout)
        {
            return CommonReader.GetReader(cnn as IDbConnection, query, parameterCollection, timeout, null) as DbDataReader;
        }

        /// <summary>
        /// Returns an instance of <see cref="System.Data.IDataReader"/>
        /// based on the object implementing <see cref="System.Data.IDbConnection"/>.
        /// </summary>
        /// <param name="cnn">The object implementing <see cref="System.Data.IDbConnection"/>.</param>
        /// <param name="query">The SELECT SQL statement.</param>
        /// <param name="parameterCollection">A list of parameters.</param>
        /// <param name="timeout">Command timeout in seconds.</param>
        public static IDataReader GetReader(IDbConnection cnn, String query, Dictionary<String, Object> parameterCollection, int timeout)
        {
            return CommonReader.GetReader(cnn, query, parameterCollection, timeout, null);
        }

        /// <summary>
        /// Returns a <see cref="System.Data.Common.DbDataReader"/>
        /// based on the <see cref="System.Data.Common.DbConnection"/>.
        /// </summary>
        /// <param name="cnn">The <see cref="System.Data.Common.DbConnection"/>.</param>
        /// <param name="query">The SELECT SQL statement.</param>
        /// <param name="parameterCollection">A list of parameters.</param>
        /// <param name="timeout">Command timeout in seconds.</param>
        /// <param name="ambientTransaction">The ambient <see cref="System.Data.Common.DbTransaction"/>.</param>
        public static DbDataReader GetReader(DbConnection cnn, String query,
            Dictionary<String, Object> parameterCollection, int timeout, DbTransaction ambientTransaction)
        {
            return GetReader(cnn as IDbConnection, query, parameterCollection, timeout, ambientTransaction as IDbTransaction) as DbDataReader;
        }

        /// <summary>
        /// Returns an instance of <see cref="System.Data.IDataReader"/>
        /// based on the object implementing <see cref="System.Data.IDbConnection"/>.
        /// </summary>
        /// <param name="cnn">The object implementing <see cref="System.Data.IDbConnection"/>.</param>
        /// <param name="query">The SELECT SQL statement.</param>
        /// <param name="parameterCollection">A list of parameters.</param>
        /// <param name="timeout">Command timeout in seconds.</param>
        /// <param name="ambientTransaction">The ambient <see cref="System.Data.IDbTransaction"/> implementation.</param>
        public static IDataReader GetReader(IDbConnection cnn, String query,
            Dictionary<String, Object> parameterCollection, int timeout, IDbTransaction ambientTransaction)
        {
            if (cnn == null) throw new ArgumentNullException("cnn", "The implementing Connection object is null.");
            if (String.IsNullOrEmpty(query)) throw new ArgumentException("The DBMS query was not specified.");

            IDataReader r = null;
            using (IDbCommand selectCommand = cnn.CreateCommand())
            {
                selectCommand.CommandType = (query.Contains("SELECT")) ? CommandType.Text : CommandType.StoredProcedure;
                selectCommand.CommandText = query;
                selectCommand.CommandTimeout = timeout;

                if (parameterCollection != null)
                {
                    IDataParameter[] parameters = CommonParameter.GetParameters(selectCommand, parameterCollection);
                    foreach (IDataParameter p in parameters)
                    {
                        selectCommand.Parameters.Add(p);
                    }
                }
                if (ambientTransaction != null) selectCommand.Transaction = ambientTransaction;

                r = selectCommand.ExecuteReader(CommandBehavior.Default);
            }
            return r;
        }

        /// <summary>
        /// Returns a <see cref="System.Xml.XPath.XPathDocument"/>
        /// based on the <see cref="System.Data.Common.DbCommand"/>.
        /// </summary>
        /// <param name="selectCommand">The <see cref="System.Data.Common.DbCommand"/>.</param>
        /// <param name="documentElement">Document element name of the XML set.</param>
        public static XPathDocument GetXPathDocument(DbCommand selectCommand, String documentElement)
        {
            return GetXPathDocument(selectCommand as IDbCommand, documentElement);
        }

        /// <summary>
        /// Returns a <see cref="System.Xml.XPath.XPathDocument"/>
        /// based on the object implementing <see cref="System.Data.IDbCommand"/>.
        /// </summary>
        /// <param name="selectCommand">The object implementing <see cref="System.Data.IDbCommand"/>.</param>
        /// <param name="documentElement">Document element name of the XML set.</param>
        public static XPathDocument GetXPathDocument(IDbCommand selectCommand, String documentElement)
        {
            if (selectCommand == null) throw new ArgumentNullException("selectCommand", "The implementing SELECT command is null.");
            if (String.IsNullOrEmpty(documentElement)) throw new ArgumentException("The Document Element name for the XPath Document was not specified.");

            XPathDocument d = null;

            using(IDataReader reader = selectCommand.ExecuteReader(CommandBehavior.Default))
            {
                d = CommonReader.GetXPathDocument(documentElement, reader);
            }

            return d;
        }

        /// <summary>
        /// Returns a <see cref="System.Xml.XPath.XPathDocument"/>
        /// based on the <see cref="System.Data.Common.DbCommand"/>.
        /// </summary>
        /// <param name="selectCommand">The <see cref="System.Data.Common.DbCommand"/>.</param>
        /// <param name="documentElement">Document element name of the XML set.</param>
        /// <param name="rowElement">Row element name of the XML set.</param>
        public static XPathDocument GetXPathDocument(DbCommand selectCommand, String documentElement, String rowElement)
        {
            return GetXPathDocument(selectCommand as IDbCommand, documentElement, rowElement);
        }

        /// <summary>
        /// Returns a <see cref="System.Xml.XPath.XPathDocument"/>
        /// based on the object implementing <see cref="System.Data.IDbCommand"/>.
        /// </summary>
        /// <param name="selectCommand">The object implementing <see cref="System.Data.IDbCommand"/>.</param>
        /// <param name="documentElement">Document element name of the XML set.</param>
        /// <param name="rowElement">Row element name of the XML set.</param>
        public static XPathDocument GetXPathDocument(IDbCommand selectCommand, String documentElement, String rowElement)
        {
            if (selectCommand == null) throw new ArgumentNullException("selectCommand", "The implementing SELECT command is null.");
            if (String.IsNullOrEmpty(documentElement)) throw new ArgumentException("The Document Element name for the XPath Document was not specified.");
            if (String.IsNullOrEmpty(rowElement)) throw new ArgumentException("The row Element name for the XPath Document was not specified.");

            XPathDocument d = null;

            using(IDataReader reader = selectCommand.ExecuteReader(CommandBehavior.Default))
            {
                d = CommonReader.GetXPathDocument(documentElement, rowElement, reader);
            }

            return d;
        }

        /// <summary>
        /// Returns a <see cref="System.Xml.XPath.XPathDocument"/>
        /// based on the <see cref="System.Data.Common.DbConnection"/>.
        /// </summary>
        /// <param name="cnn">The <see cref="System.Data.Common.DbConnection"/>.</param>
        /// <param name="query">The SELECT SQL statement.</param>
        /// <param name="documentElement">Document element name of the XML set.</param>
        public static XPathDocument GetXPathDocument(DbConnection cnn, String query, String documentElement)
        {
            return GetXPathDocument(cnn as IDbConnection, query, documentElement);
        }

        /// <summary>
        /// Returns a <see cref="System.Xml.XPath.XPathDocument"/>
        /// based on the object implementing <see cref="System.Data.IDbConnection"/>.
        /// </summary>
        /// <param name="cnn">The object implementing <see cref="System.Data.IDbConnection"/>.</param>
        /// <param name="query">The SELECT SQL statement.</param>
        /// <param name="documentElement">Document element name of the XML set.</param>
        public static XPathDocument GetXPathDocument(IDbConnection cnn, String query, String documentElement)
        {
            if (cnn == null) throw new ArgumentNullException("cnn", "The implementing Connection object is null.");
            if (String.IsNullOrEmpty(query)) throw new ArgumentException("The DBMS query was not specified.");
            if (String.IsNullOrEmpty(documentElement)) throw new ArgumentException("The Document Element name for the XPath Document was not specified.");

            XPathDocument d = null;
            using (IDbCommand selectCommand = cnn.CreateCommand())
            {
                selectCommand.CommandType = System.Data.CommandType.Text;
                selectCommand.CommandText = query;

                using (IDataReader reader = selectCommand.ExecuteReader(CommandBehavior.SingleRow))
                {
                    d = CommonReader.GetXPathDocument(documentElement, reader);
                }
            }

            return d;
        }

        /// <summary>
        /// Returns a <see cref="System.Xml.XPath.XPathDocument"/>
        /// based on the <see cref="System.Data.Common.DbConnection"/>.
        /// </summary>
        /// <param name="cnn">The <see cref="System.Data.Common.DbConnection"/>.</param>
        /// <param name="query">The SELECT SQL statement.</param>
        /// <param name="documentElement">Document element name of the XML set.</param>
        /// <param name="rowElement">Row element name of the XML set.</param>
        public static XPathDocument GetXPathDocument(DbConnection cnn, String query, String documentElement, String rowElement)
        {
            return GetXPathDocument(cnn as IDbConnection, query, documentElement, rowElement);
        }

        /// <summary>
        /// Returns a <see cref="System.Xml.XPath.XPathDocument"/>
        /// based on the object implementing <see cref="System.Data.IDbConnection"/>.
        /// </summary>
        /// <param name="cnn">The object implementing <see cref="System.Data.IDbConnection"/>.</param>
        /// <param name="query">The SELECT SQL statement.</param>
        /// <param name="documentElement">Document element name of the XML set.</param>
        /// <param name="rowElement">Row element name of the XML set.</param>
        public static XPathDocument GetXPathDocument(IDbConnection cnn, String query, String documentElement, String rowElement)
        {
            if (cnn == null) throw new ArgumentNullException("cnn", "The implementing Connection object is null.");
            if (String.IsNullOrEmpty(query)) throw new ArgumentException("The DBMS query was not specified.");
            if (String.IsNullOrEmpty(documentElement)) throw new ArgumentException("The Document Element name for the XPath Document was not specified.");
            if (String.IsNullOrEmpty(rowElement)) throw new ArgumentException("The row Element name for the XPath Document was not specified.");

            XPathDocument d = null;
            using (IDbCommand selectCommand = cnn.CreateCommand())
            {
                selectCommand.CommandType = System.Data.CommandType.Text;
                selectCommand.CommandText = query;

                using (IDataReader reader = selectCommand.ExecuteReader(CommandBehavior.Default))
                {
                    d = CommonReader.GetXPathDocument(documentElement, rowElement, reader);
                }
            }
            return d;
        }

        /// <summary>
        /// Returns a <see cref="System.Xml.XPath.XPathDocument"/>
        /// based on the data of the type <see cref="System.Data.Common.DbDataReader"/>.
        /// </summary>
        /// <param name="documentElement">Document element name of the XML set.</param>
        /// <param name="reader">Data of the type <see cref="System.Data.Common.DbDataReader"/>.</param>
        /// <remarks>This member is designed for one-row data sets.</remarks>
        public static XPathDocument GetXPathDocument(String documentElement, DbDataReader reader)
        {
            return GetXPathDocument(documentElement, reader as IDataReader);
        }

        /// <summary>
        /// Returns a <see cref="System.Xml.XPath.XPathDocument"/>
        /// based on the data of the type <see cref="System.Data.Common.DbDataReader"/>.
        /// </summary>
        /// <param name="documentElement">Document element name of the XML set.</param>
        /// <param name="reader">Data of the type <see cref="System.Data.IDataReader"/>.</param>
        /// <remarks>This member is designed for one-row data sets.</remarks>
        public static XPathDocument GetXPathDocument(String documentElement, IDataReader reader)
        {
            if (String.IsNullOrEmpty(documentElement)) throw new ArgumentException("The Document Element name for the XPath Document was not specified.");
            if (reader == null) throw new ArgumentNullException("reader", "The Common Data Reader is null.");
            if (reader.IsClosed) throw new ArgumentException("The implementing Data Reader is closed.", "reader");

            MemoryStream ms = new MemoryStream();
            XPathDocument xpDoc = null;

            using (XmlTextWriter writer = new XmlTextWriter(ms, Encoding.UTF8))
            {
                writer.WriteStartDocument();
                writer.WriteStartElement(documentElement);

                if (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        writer.WriteRaw(String.Format("<{0}>{1}</{0}>", reader.GetName(i), reader[i].ToString()));
                    }
                }

                writer.WriteFullEndElement();
                writer.WriteEndDocument();

                writer.Flush();
                ms.Position = 0;

                xpDoc = new XPathDocument(ms);

            }

            return xpDoc;
        }

        /// <summary>
        /// Returns a <see cref="System.Xml.XPath.XPathDocument"/>
        /// based on the data of the type <see cref="System.Data.Common.DbDataReader"/>.
        /// </summary>
        /// <param name="documentElement">Document element name of the XML set.</param>
        /// <param name="rowElement">Row element name of the XML set.</param>
        /// <param name="reader">Data of the type <see cref="System.Data.Common.DbDataReader"/>.</param>
        public static XPathDocument GetXPathDocument(String documentElement, String rowElement, DbDataReader reader)
        {
            return GetXPathDocument(documentElement, rowElement, reader as IDataReader);
        }

        /// <summary>
        /// Returns a <see cref="System.Xml.XPath.XPathDocument"/>
        /// based on the data of the type <see cref="System.Data.Common.DbDataReader"/>.
        /// </summary>
        /// <param name="documentElement">Document element name of the XML set.</param>
        /// <param name="rowElement">Row element name of the XML set.</param>
        /// <param name="reader">Set implementing <see cref="System.Data.IDataReader"/>.</param>
        public static XPathDocument GetXPathDocument(String documentElement, String rowElement, IDataReader reader)
        {
            if (String.IsNullOrEmpty(documentElement)) throw new ArgumentException("The Document Element name for the XPath Document was not specified.");
            if (String.IsNullOrEmpty(rowElement)) throw new ArgumentException("The row Element name for the XPath Document was not specified.");
            if (reader == null) throw new ArgumentNullException("reader", "The implementing Data Reader is null.");
            if (reader.IsClosed) throw new ArgumentException("The implementing Data Reader is closed.", "reader");

            MemoryStream ms = new MemoryStream();
            XPathDocument xpDoc = null;

            using (XmlTextWriter writer = new XmlTextWriter(ms, Encoding.UTF8))
            {
                writer.WriteStartDocument();
                writer.WriteStartElement(documentElement);

                while (reader.Read())
                {
                    writer.WriteStartElement(rowElement);
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        writer.WriteRaw(String.Format("<{0}>{1}</{0}>", reader.GetName(i), reader[i].ToString()));
                    }
                    writer.WriteEndElement();
                }

                writer.WriteFullEndElement();
                writer.WriteEndDocument();

                writer.Flush();
                ms.Position = 0;

                xpDoc = new XPathDocument(ms);

            }

            return xpDoc;
        }
    }
}
